package com.kingyun.gpsinspection.purificationservice.services.jsm.innerservice.impl.operateunit;

import com.kingyun.gpsinspection.purificationservice.common.enums.StepStageEnum;
import com.kingyun.gpsinspection.purificationservice.common.poi.Step.StepIndex;
import com.kingyun.gpsinspection.purificationservice.common.poi.Step.StepPic;
import com.kingyun.gpsinspection.purificationservice.common.poi.Step.StepType;
import com.kingyun.gpsinspection.purificationservice.facade.model.jsm.SingleWorkStep;
import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * 导入操作步骤的工具类
 * @author zjh
 * @since 20170314
 */
public class OperateCard {


    /**
     * 解析操作卡
     * @param excel
     * @throws Exception
     */
    public static List<SingleWorkStep> getWorkCard(MultipartFile excel) throws Exception {
        List<SingleWorkStep> result=Lists.newArrayList();
        Workbook wb=null;
        List<CellRangeAddress> list = Lists.newArrayList();
        if (excel.getOriginalFilename().toLowerCase().endsWith("xls")) {
            wb = new HSSFWorkbook(excel.getInputStream());
        } else if (excel.getOriginalFilename().toLowerCase().endsWith("xlsx")) {
            wb = new XSSFWorkbook(excel.getInputStream());
        }
        Sheet sheet = wb.getSheetAt(0);
        getCombineCell(sheet,list);
        Map<String, StepIndex> position = getPosition(sheet,list);
        Map<Integer, List<StepPic>> allPictures=null;
        if (excel.getOriginalFilename().toLowerCase().endsWith("xls")) {
            allPictures=findAllPictureDate((HSSFSheet) sheet, 0);
        } else if (excel.getOriginalFilename().toLowerCase().endsWith("xlsx")) {
            allPictures=findAllPictureDateOfXssf((XSSFSheet) sheet, 0);
        }

        String[] commonPos={"stepName"};
        String[] operatePos={"stepName","keyPoint","potentialRisk","controlMeasures","","isImportant"};
        StepIndex advStepIndex = position.get(StepType.ADV.getCode());
        StepIndex merStepIndex = position.get(StepType.MER.getCode());
        StepIndex perStepIndex = position.get(StepType.PER.getCode());
        StepIndex opeStepIndex = position.get(StepType.OPE.getCode());
        StepIndex taiStepIndex = position.get(StepType.TAI.getCode());
        int index=1;
        //风险阶段数据提取
        if(advStepIndex!=null){
            List<SingleWorkStep> advList = getWorkCardStepData(sheet,list,advStepIndex.getBegin(),advStepIndex.getEnd(),commonPos,allPictures);
            if(advList!=null&&advList.size()>0){
                for(SingleWorkStep adv:advList){
                    if(!Strings.isNullOrEmpty(adv.getStepName())){
                        adv.setNum(index++);
                        adv.setStepType(StepStageEnum.AVD.getCode());
                        result.add(adv);
                    }
                }
            }

        }

        //应急阶段数据提取
        if(merStepIndex!=null){
            List<SingleWorkStep> merList = getWorkCardStepData(sheet,list,merStepIndex.getBegin(),merStepIndex.getEnd(),commonPos,allPictures);
            index=1;
            if(merList!=null&&merList.size()>0){
                for(SingleWorkStep merge:merList){
                    if(!Strings.isNullOrEmpty(merge.getStepName())){
                        merge.setNum(index++);
                        merge.setStepType(StepStageEnum.MERGE.getCode());
                        result.add(merge);
                    }
                }
            }
        }

        //准备阶段数据提权
        if(perStepIndex!=null){
            List<SingleWorkStep> perList = getWorkCardStepData(sheet,list,perStepIndex.getBegin(),perStepIndex.getEnd(),commonPos,allPictures);
            index=1;
            if(perList!=null&&perList.size()>0){
                for(SingleWorkStep pepare:perList){
                    if(!Strings.isNullOrEmpty(pepare.getStepName())){
                        pepare.setNum(index++);
                        pepare.setStepType(StepStageEnum.PRAPARE.getCode());
                        result.add(pepare);
                    }
                }
            }
        }

        //操作阶段数据提取
        if(opeStepIndex!=null){
            List<SingleWorkStep> opeList = getWorkCardStepData(sheet,list,opeStepIndex.getBegin(),opeStepIndex.getEnd(),operatePos,allPictures);
            index=1;
            if(opeList!=null&&opeList.size()>0){
                for(SingleWorkStep operate:opeList){
                    if(!Strings.isNullOrEmpty(operate.getStepName())){
                        operate.setNum(index++);
                        operate.setStepType(StepStageEnum.OPERATE.getCode());
                        result.add(operate);
                    }
                }
            }
        }

        //收尾阶段数据提取
        if(taiStepIndex!=null){
            List<SingleWorkStep> taiList = getWorkCardStepData(sheet,list,taiStepIndex.getBegin(),taiStepIndex.getEnd(),commonPos,allPictures);
            index=1;
            if(taiList!=null&&taiList.size()>0){
                for(SingleWorkStep end:taiList){
                    if(!Strings.isNullOrEmpty(end.getStepName())){
                        end.setNum(index++);
                        end.setStepType(StepStageEnum.ENDING.getCode());
                        result.add(end);
                    }
                }
            }
        }

        return result;
    }


    /**
     * 得到阶段的开始和结束索引
     * @param sheet
     * @param list
     * @return
     */
    public static Map<String,StepIndex> getPosition(Sheet sheet,List<CellRangeAddress> list){
        Map<String,StepIndex> result=Maps.newHashMap();
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <=lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if(row!=null){
                Cell cell=row.getCell(0);
                if(cell!=null){
                    String title=cell.getStringCellValue();
                    if(title!=null && !"".equals(title)){
                        setStepIndex(cell, title, result, list);
                    }
                }
            }
        }
        return result;

    }

    /**
     * 设置阶段的索引
     * @param cell
     * @param title
     * @param result
     * @param list
     */
    public static void setStepIndex(Cell cell,String title,Map<String,StepIndex> result,List<CellRangeAddress> list){
        Integer begin=0;
        Integer end=0;
        CellRangeAddress combineCell = isCombineCell(list,cell);//判断当前的单元格是不是合并以后的单元格
        if(combineCell!=null){ //是合并单元格
            begin = combineCell.getFirstRow();
            end = combineCell.getLastRow();
        }else{//不是合并单元格
            begin=cell.getRowIndex();
            end=cell.getRowIndex();
        }
        if(title.contains(StepType.ADV.getName())){  //风险阶段
            result.put(StepType.ADV.getCode(), new StepIndex(begin,end));
        }else if(title.contains(StepType.MER.getName())){ //应急阶段
            result.put(StepType.MER.getCode(), new StepIndex(begin,end));
        }else if(title.contains(StepType.PER.getName())){ //准备阶段
            result.put(StepType.PER.getCode(), new StepIndex(begin,end));
        }else if(title.contains(StepType.OPE.getName())){//操作阶段
            result.put(StepType.OPE.getCode(), new StepIndex(begin+1,end));
        }else if(title.contains(StepType.TAI.getName())){//收尾阶段
            result.put(StepType.TAI.getCode(), new StepIndex(begin,end));
        }
    }



    /**
     * 得到操作卡的操作步骤的数据
     * @param sheet
     * @param list
     * @return
     */
    public static List<SingleWorkStep> getWorkCardStepData(Sheet sheet,List<CellRangeAddress> list,Integer begin,Integer end,String[] pos,Map<Integer, List<StepPic>> pics){
        List<SingleWorkStep> singleWorkSteps=Lists.newArrayList();
        Class<SingleWorkStep> clazz=SingleWorkStep.class;
        for(int i=begin;i<=end;i++){
            try {
                SingleWorkStep newInstance = clazz.newInstance();
                Row row = sheet.getRow(i);
                if(row!=null){
                    int index=0;
                    for(int j=1;j<row.getLastCellNum();j++){
                        if(index<pos.length){
                            String fieldName=pos[index];
                            if(!Strings.isNullOrEmpty(fieldName)){
                                Field field = clazz.getDeclaredField(fieldName);
                                field.setAccessible(true);
                                Cell cell = row.getCell(j);
                                if(cell!=null){
                                    CellRangeAddress combineCell = isCombineCell(list,cell);
                                    if(combineCell!=null){
                                        field.set(newInstance, fixStr(cell.getStringCellValue()));
                                        j=combineCell.getLastColumn();
                                    }else{
                                        if(!"isImportant".equals(field.getName())){
                                            field.set(newInstance, fixStr(cell.getStringCellValue()));
                                        }else{
                                            if("□".equals(cell.getStringCellValue())){
                                                field.set(newInstance, 1);
                                            }else{
                                                field.set(newInstance, 0);
                                            }
                                        }
                                    }
                                }
                            }
                        }else{
                            break;
                        }
                        index++;
                    }
                    if(pics!=null&&pics.size()>0){
                        List<StepPic> rowPics = pics.get(i);
                        if(rowPics!=null){
                            Field picField=clazz.getDeclaredField("pics");
                            picField.setAccessible(true);
                            picField.set(newInstance, rowPics);
                        }
                    }
                    singleWorkSteps.add(newInstance);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("解析EXCEL出错");
            }
        }
        return singleWorkSteps;
    }

    /**
     * 解析巡检
     * @param excel
     * @throws Exception
     */
    public static List<SingleWorkStep> getInspection(MultipartFile excel) throws Exception {
         List<SingleWorkStep> result=Lists.newArrayList();
        List<CellRangeAddress> list=Lists.newArrayList();
        Workbook wb=null;

        if (excel.getOriginalFilename().toLowerCase().endsWith("xls")) {
            wb = new HSSFWorkbook(excel.getInputStream());
        } else if (excel.getOriginalFilename().toLowerCase().endsWith("xlsx")) {
            wb = new XSSFWorkbook(excel.getInputStream());
        }
        Sheet sheet =  wb.getSheetAt(0);
        Map<Integer, List<StepPic>> allPictures=null;
        if (excel.getOriginalFilename().toLowerCase().endsWith("xls")) {
            allPictures = findAllPictureDate((HSSFSheet) sheet,0);
        } else if (excel.getOriginalFilename().toLowerCase().endsWith("xlsx")) {
            allPictures = findAllPictureDateOfXssf((XSSFSheet) sheet,0);
        }

        getCombineCell(sheet,list);
        Map<String, StepIndex> position = getPosition(sheet,list);
        String[] commonPos = { "stepName" };
        String[] operatePos = { "category", "stepName" };
        String[] endPos = {"stepName" };


        StepIndex advStepIndex = position.get(StepType.ADV.getCode());
        StepIndex merStepIndex = position.get(StepType.MER.getCode());
        StepIndex perStepIndex = position.get(StepType.PER.getCode());
        StepIndex opeStepIndex = position.get(StepType.OPE.getCode());
        StepIndex taiStepIndex = position.get(StepType.TAI.getCode());
        int index=1;
        //风险阶段数据提取
        if(advStepIndex!=null){
            List<SingleWorkStep> advList = getInspectionStepData(sheet,list,advStepIndex.getBegin(),advStepIndex.getEnd(),commonPos,allPictures);
            if(advList!=null&&advList.size()>0){
                for(SingleWorkStep adv:advList){
                    if(!Strings.isNullOrEmpty(adv.getStepName())){
                        adv.setNum(index++);
                        adv.setStepType(StepStageEnum.AVD.getCode());
                        result.add(adv);
                    }
                }
            }
        }

        //应急阶段数据提取
        if(merStepIndex!=null){
            List<SingleWorkStep> merList = getInspectionStepData(sheet,list,merStepIndex.getBegin(),merStepIndex.getEnd(),commonPos,allPictures);
            index=1;
            if(merList!=null&&merList.size()>0){
                for(SingleWorkStep merge:merList){
                    if(!Strings.isNullOrEmpty(merge.getStepName())){
                        merge.setNum(index++);
                        merge.setStepType(StepStageEnum.MERGE.getCode());
                        result.add(merge);
                    }
                }
            }
        }

        //准备阶段数据提取
        if(perStepIndex!=null){
            List<SingleWorkStep> perList = getInspectionStepData(sheet,list,perStepIndex.getBegin(),perStepIndex.getEnd(),commonPos,allPictures);
            index=1;
            if(perList!=null&&perList.size()>0){
                for(SingleWorkStep pepare:perList){
                    if(!Strings.isNullOrEmpty(pepare.getStepName())){
                        pepare.setNum(index++);
                        pepare.setStepType(StepStageEnum.PRAPARE.getCode());
                        result.add(pepare);
                    }
                }
            }
        }

        //操作阶段数据提取
        if(opeStepIndex!=null){
            List<SingleWorkStep> opeList = getInspectionTypeStepData(sheet,list,opeStepIndex.getBegin(),opeStepIndex.getEnd(),operatePos,allPictures);
            index=1;
            if(opeList!=null&&opeList.size()>0){
                for(SingleWorkStep operate:opeList){
                    if(!Strings.isNullOrEmpty(operate.getStepName())){
                        operate.setNum(index++);
                        operate.setStepType(StepStageEnum.OPERATE.getCode());
                        result.add(operate);
                    }
                }
            }
        }

        //收尾阶段数据提取
        if(taiStepIndex!=null){
            List<SingleWorkStep> taiList = getInspectionStepData(sheet,list,taiStepIndex.getBegin(),taiStepIndex.getEnd(),commonPos,allPictures);
            index=1;
            if(taiList!=null&&taiList.size()>0){
                for(SingleWorkStep end:taiList){
                    if(!Strings.isNullOrEmpty(end.getStepName())){
                        end.setNum(index++);
                        end.setStepType(StepStageEnum.ENDING.getCode());
                        result.add(end);
                    }
                }
            }

        }
        return result;
    }




    /**
     * 得到巡回检查的操作步骤的数据
     * @param sheet
     * @param list
     * @return
     */
    public static List<SingleWorkStep> getInspectionStepData(Sheet sheet,List<CellRangeAddress> list,Integer begin,Integer end,String[] pos,Map<Integer, List<StepPic>> pics){
        List<SingleWorkStep> singleWorkSteps=Lists.newArrayList();
        Class<SingleWorkStep> clazz=SingleWorkStep.class;

        for(int i=begin;i<=end;i++){
            try {
                SingleWorkStep newInstance = clazz.newInstance();
                Row row = sheet.getRow(i);
                if(row!=null){
                    int index=0;
                    for(int j=2;j<row.getLastCellNum();j++){
                        if(index<pos.length){
                            String fieldName=pos[index];
                            if(!Strings.isNullOrEmpty(fieldName)){
                                Field field = clazz.getDeclaredField(fieldName);
                                field.setAccessible(true);
                                Cell cell = row.getCell(j);
                                if(cell!=null){
                                    CellRangeAddress combineCell = isCombineCell(list,cell);
                                    if(combineCell!=null){
                                        field.set(newInstance, fixStr(cell.getStringCellValue()));
                                        j=combineCell.getLastColumn();
                                    }else{
                                        field.set(newInstance, fixStr(cell.getStringCellValue()));
                                    }
                                }
                            }
                        }else{
                            break;
                        }
                        index++;
                    }

                    if(pics!=null&&pics.size()>0){
                        List<StepPic> rowPics = pics.get(i);
                        if(rowPics!=null){
                            Field picField=clazz.getDeclaredField("pics");
                            picField.setAccessible(true);
                            picField.set(newInstance, rowPics);
                        }
                    }
                    singleWorkSteps.add(newInstance);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("解析EXCEL出错");
            }

        }

        return singleWorkSteps;
    }




    /**
     * 得到巡回检查类别相关的操作步骤
     * @return
     */
    public static List<SingleWorkStep> getInspectionTypeStepData(Sheet sheet,List<CellRangeAddress> list,Integer begin,Integer end,String[] pos,Map<Integer, List<StepPic>> pics){
        List<SingleWorkStep> singleWorkSteps=Lists.newArrayList();
        Class<SingleWorkStep> clazz=SingleWorkStep.class;
        Boolean isRowCombile=false;
        for(int i=begin;i<=end;i++){
            try {
                SingleWorkStep newInstance = clazz.newInstance();
                Field typeField = clazz.getDeclaredField("stepType");
                typeField.setAccessible(true);
                //设置类型
                typeField.set(newInstance,StepStageEnum.OPERATE.getCode());
                Row row = sheet.getRow(i);
                if(row!=null){
                    int index=0;
                    for(int j=1;j<row.getLastCellNum();j++){
                        if(index<pos.length){
                            String fieldName=pos[index];
                            if(!Strings.isNullOrEmpty(fieldName)){
                                Field field = clazz.getDeclaredField(fieldName);
                                field.setAccessible(true);
                                Cell cell = row.getCell(j);
                                if(cell!=null){
                                    CellRangeAddress combineCell = isCombineCell(list,cell);
                                    if(combineCell!=null){
                                        isRowCombile=true;
                                        //列合并
                                        if(combineCell.getFirstRow()==combineCell.getLastRow()){
                                            isRowCombile=false;
                                            field.set(newInstance,fixStr(cell.getStringCellValue()));
                                            j=combineCell.getLastColumn();
                                        }else{//行合并
                                            isRowCombile=true;
                                            String category=fixStr(cell.getStringCellValue());
                                            j=combineCell.getLastColumn();
                                            j++;
                                            for(int k=combineCell.getFirstRow();k<=combineCell.getLastRow();k++){
                                                fieldName=pos[index];
                                                newInstance=clazz.newInstance();
                                                field = clazz.getDeclaredField(fieldName);//大类
                                                field.setAccessible(true);
                                                field.set(newInstance,category);
                                                typeField = clazz.getDeclaredField("stepType");//步骤类型
                                                typeField.setAccessible(true);
                                                //设置类型
                                                typeField.set(newInstance,StepStageEnum.OPERATE.getCode());
                                                index++;
                                                fieldName=pos[index];
                                                field = clazz.getDeclaredField(fieldName);//步骤名称
                                                field.setAccessible(true);
                                                field.set(newInstance,  fixStr(sheet.getRow(k).getCell(j).getStringCellValue()));
                                                if(pics!=null&&pics.size()>0){
                                                    List<StepPic> rowPics = pics.get(k);
                                                    if(rowPics!=null){
                                                        Field picField=clazz.getDeclaredField("pics");
                                                        picField.setAccessible(true);
                                                        picField.set(newInstance, rowPics);
                                                    }
                                                }
                                                singleWorkSteps.add(newInstance);
                                                index--;
                                            }
                                            index++;
                                            i=combineCell.getLastRow();
                                        }
                                    }else{
                                        isRowCombile=false;
                                        field.set(newInstance, fixStr(cell.getStringCellValue()));
                                    }
                                }
                            }
                        }else{
                            break;
                        }
                        index++;
                    }
                    if(!isRowCombile){
                        if(pics!=null&&pics.size()>0){
                            List<StepPic> rowPics = pics.get(i);
                            if(rowPics!=null){
                                Field picField=clazz.getDeclaredField("pics");
                                picField.setAccessible(true);
                                picField.set(newInstance, rowPics);
                            }
                        }
                        singleWorkSteps.add(newInstance);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("解析EXCEL出错");
            }

        }

        return singleWorkSteps;
    }


    /**
     * 得到合并的单元格
     * @param sheet
     * @param list
     */
    public static   void getCombineCell(Sheet sheet, List<CellRangeAddress> list) {
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            // 获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
    }

    /**
     * 得到Excel的数据
     * @param sheet
     * @param list
     * @param beginIndex
     * @param endIndex
     * @param type
     * @param pos
     * @param clazz
     * @return
     * @throws Exception
     */
    public static  List<SingleWorkStep> getOperate(HSSFSheet sheet,List<CellRangeAddress>
            list,int beginIndex,int endIndex,String type,String[] pos,Class<SingleWorkStep> clazz) throws Exception{
        List<SingleWorkStep> singleWorkSteps=Lists.newArrayList();
        for (int i = beginIndex; i <= endIndex; i++) {
            SingleWorkStep newInstance = clazz.newInstance();
            Field typeField = clazz.getDeclaredField("stepType");
            typeField.setAccessible(true);
            //设置类型
            if("operate".equals(type)){
                typeField.set(newInstance, StepStageEnum.OPERATE.getCode());
            }else if("pepare".equals(type)){
                typeField.set(newInstance,StepStageEnum.PRAPARE.getCode());
            }else if("end".equals(type)){
                typeField.set(newInstance,StepStageEnum.ENDING.getCode());
            }else if("adv".equals(type)){
                typeField.set(newInstance,StepStageEnum.AVD.getCode());
            }else if("merge".equals(type)){
                typeField.set(newInstance,StepStageEnum.MERGE.getCode());
            }
            HSSFRow row = sheet.getRow(i);
            int index=0;
            for(int j=1;j<row.getLastCellNum();j++){
                if(index<pos.length){
                    String fieldName=pos[index];
                    if(!"".equals(fieldName)){
                        Field field = clazz.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        HSSFCell cell = row.getCell(j);
                        CellRangeAddress combineCell = isCombineCell(list,cell);
                        if(combineCell!=null){
                            field.set(newInstance, getCellValue(cell));
                            j=combineCell.getLastColumn();
                        }else{
                            if(!"isImportant".equals(field.getName())){
                                field.set(newInstance, getCellValue(cell));
                            }else{
                                if("□".equals(cell.getStringCellValue())){
                                    field.set(newInstance, 1);
                                }else{
                                    field.set(newInstance, 0);
                                }
                            }
                        }
                    }
                }else{
                    break;
                }
                index++;
            }
            singleWorkSteps.add(newInstance);
        }
        return singleWorkSteps;
    }


    /**
     * 得到巡检的内容
     * @param sheet
     * @param list
     * @param beginIndex
     * @param endIndex
     * @param type
     * @param pos
     * @param clazz
     * @return
     * @throws Exception
     */
    public static List<SingleWorkStep> getOperate2(HSSFSheet sheet,List<CellRangeAddress>
            list,int beginIndex,int endIndex,String type,String[] pos,Class<SingleWorkStep> clazz) throws Exception{
        List<SingleWorkStep> singleWorkSteps=Lists.newArrayList();
        Boolean isRowCombile=false;
        for (int i = beginIndex; i <= endIndex; i++) {
            SingleWorkStep newInstance = clazz.newInstance();
            Field typeField = clazz.getDeclaredField("stepType");
            typeField.setAccessible(true);
            //设置类型
            typeField.set(newInstance,StepStageEnum.OPERATE.getCode());
            HSSFRow row = sheet.getRow(i);
            int index=0;
            for(int j=1;j<row.getLastCellNum();j++){
                if(index<pos.length){
                    String fieldName=pos[index];
                    if(!"".equals(fieldName)){
                        Field field = clazz.getDeclaredField(fieldName);
                        field.setAccessible(true);
                        HSSFCell cell = row.getCell(j);
                        CellRangeAddress combineCell = isCombineCell(list,cell);
                        if(combineCell!=null){
                            isRowCombile=true;
                            //列合并
                            if(combineCell.getFirstRow()==combineCell.getLastRow()){
                                isRowCombile=false;
                                field.set(newInstance,cell.getStringCellValue());
                                j=combineCell.getLastColumn();
                            }else{//行合并
                                isRowCombile=true;
                                String category=cell.getStringCellValue();
                                j=combineCell.getLastColumn();
                                j++;

                                //TODO 有潜在BUG
                                for(int k=combineCell.getFirstRow();k<=combineCell.getLastRow();k++){
                                    fieldName=pos[index];
                                    newInstance=clazz.newInstance();
                                    field = clazz.getDeclaredField(fieldName);//大类
                                    field.setAccessible(true);
                                    field.set(newInstance,category);
                                    typeField = clazz.getDeclaredField("stepType");//步骤类型
                                    typeField.setAccessible(true);
                                    //设置类型
                                    typeField.set(newInstance,StepStageEnum.OPERATE.getCode());
                                    index++;
                                    fieldName=pos[index];
                                    field = clazz.getDeclaredField(fieldName);//步骤名称
                                    field.setAccessible(true);
                                    field.set(newInstance,  sheet.getRow(k).getCell(j).getStringCellValue());
                                    singleWorkSteps.add(newInstance);
                                    index--;
                                }
                                index++;
                                i=combineCell.getLastRow();
                            }
                        }else{
                            isRowCombile=false;
                            field.set(newInstance,cell.getStringCellValue());
                        }
                    }
                }else{
                    break;
                }
                index++;
            }
            if(!isRowCombile){
                singleWorkSteps.add(newInstance);
            }
        }
        return singleWorkSteps;
    }


    /**
     * 判断是不是合并单元格
     * @param listCombineCell
     * @param cell
     * @return
     */
    public static CellRangeAddress isCombineCell(List<CellRangeAddress> listCombineCell, Cell cell) {
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                    return ca;
                }
            }
        }
        return null;
    }

    /**
     * 获取单元格值
     *
     *
     *            获取单元格列号
     * @return 单元格值
     */
    public static Object getCellValue(Cell cell) {
        Object val = "";
        try {
            if (cell != null) {
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    val = cell.getNumericCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }


    /**
     * 得到某个sheet(2003版本) 的图片
     * @param sheet
     * @param firstColumn
     * @return
     */
    public static Map<Integer, List<StepPic>> findAllPictureDate(HSSFSheet sheet, int firstColumn) {
        Map<Integer, List<StepPic>> dataMap = Maps.newHashMap();

        // 处理sheet中的图形
        HSSFPatriarch hssfPatriarch = sheet.getDrawingPatriarch();
        // 获取所有的形状图

        if(hssfPatriarch!=null){
            List<HSSFShape> shapes = hssfPatriarch.getChildren();

            if (shapes!=null&&shapes.size() > 0) {

                dataMap = new HashMap<Integer, List<StepPic>>();

                List<StepPic> pictureDataList = null;

                for (HSSFShape sp : shapes) {
                    if (sp instanceof HSSFPicture) {
                        // 转换
                        HSSFPicture picture = (HSSFPicture) sp;
                        // 获取图片数据
                        HSSFPictureData pictureData = picture.getPictureData();
                        // 图形定位
                        if (picture.getAnchor() instanceof HSSFClientAnchor) {

                            HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();
                            // 获取图片所在行作为key值,插入图片时，默认图片只占一行的单个格子，不能超出格子边界
                            int row1 = anchor.getRow1();
                            int rowNum = row1;

                            if (dataMap.get(rowNum) != null) {
                                pictureDataList = dataMap.get(rowNum);
                            } else {
                                pictureDataList = new ArrayList<StepPic>();
                            }
                            if (firstColumn <= anchor.getRow1() /*&& anchor.getCol2() <= endColumn*/) {
                                StepPic stepPic=new StepPic();
                                stepPic.setSuffix(pictureData.suggestFileExtension());
                                stepPic.setData(pictureData.getData());
                                pictureDataList.add(stepPic);
                                dataMap.put(rowNum, pictureDataList);
                                // 测试部分
                                int row2 = anchor.getRow2();
                                short col1 = anchor.getCol1();
                                short col2 = anchor.getCol2();
                                int dx1 = anchor.getDx1();
                                int dx2 = anchor.getDx2();
                                int dy1 = anchor.getDy1();
                                int dy2 = anchor.getDy2();
                                System.out.println(pictureData.suggestFileExtension());
                                System.out.println(
                                        "row1: " + row1 + " , row2: " + row2 + " , col1: " + col1 + " , col2: " + col2);
                                System.out.println("dx1: " + dx1 + " , dx2: " + dx2 + " , dy1: " + dy1 + " , dy2: " + dy2);
                            }

                        }
                    }
                }
            }

            System.out.println("********图片数量明细 START********");
            int t = 0;
            if (dataMap != null) {
                t = dataMap.keySet().size();
            }
            if (t > 0) {
                for (Integer key : dataMap.keySet()) {
                    System.out.println("第 " + key + " 行， 有图片： " + dataMap.get(key).size() + " 张");
                }
            } else {
                System.out.println("Excel表中没有图片!");
            }
            System.out.println("********图片数量明细 END ********");
        }

        return dataMap;
    }




    /**
     * 得到某个sheet(2007以上版本) 的图片
     * @param sheet
     * @param firstColumn
     * @return
     */
    public static Map<Integer, List<StepPic>> findAllPictureDateOfXssf(XSSFSheet sheet, int firstColumn) {

        Map<Integer, List<StepPic>> dataMap = null;

        // 处理sheet中的图形
        XSSFDrawing drawingPatriarch = sheet.getDrawingPatriarch();
        // 获取所有的形状图
        List<XSSFShape> shapes =drawingPatriarch.getShapes();

        if (shapes.size() > 0) {

            dataMap = new HashMap<Integer, List<StepPic>>();

            List<StepPic> pictureDataList = null;

            for (XSSFShape sp : shapes) {
                if (sp instanceof XSSFPicture) {
                    // 转换
                    XSSFPicture picture = (XSSFPicture) sp;
                    // 获取图片数据
                    XSSFPictureData pictureData = picture.getPictureData();
                    // 图形定位
                    if (picture.getAnchor() instanceof XSSFClientAnchor) {

                        XSSFClientAnchor anchor = (XSSFClientAnchor) picture.getAnchor();
                        // 获取图片所在行作为key值,插入图片时，默认图片只占一行的单个格子，不能超出格子边界
                        int row1 = anchor.getRow1();
                        int rowNum = row1;

                        if (dataMap.get(rowNum) != null) {
                            pictureDataList = dataMap.get(rowNum);
                        } else {
                            pictureDataList = new ArrayList<StepPic>();
                        }
                        if (firstColumn <= anchor.getRow1() /*&& anchor.getCol2() <= endColumn*/) {
                            StepPic stepPic=new StepPic();
                            stepPic.setSuffix(pictureData.suggestFileExtension());
                            stepPic.setData(pictureData.getData());
                            pictureDataList.add(stepPic);
                            dataMap.put(rowNum, pictureDataList);
                            // 测试部分
                            int row2 = anchor.getRow2();
                            short col1 = anchor.getCol1();
                            short col2 = anchor.getCol2();
                            int dx1 = anchor.getDx1();
                            int dx2 = anchor.getDx2();
                            int dy1 = anchor.getDy1();
                            int dy2 = anchor.getDy2();
                            System.out.println(pictureData.suggestFileExtension());
                            System.out.println(
                                    "row1: " + row1 + " , row2: " + row2 + " , col1: " + col1 + " , col2: " + col2);
                            System.out.println("dx1: " + dx1 + " , dx2: " + dx2 + " , dy1: " + dy1 + " , dy2: " + dy2);
                        }

                    }
                }
            }
        }

        System.out.println("********图片数量明细 START********");
        int t = 0;
        if (dataMap != null) {
            t = dataMap.keySet().size();
        }
        if (t > 0) {
            for (Integer key : dataMap.keySet()) {
                System.out.println("第 " + key + " 行， 有图片： " + dataMap.get(key).size() + " 张");
            }
        } else {
            System.out.println("Excel表中没有图片!");
        }
        System.out.println("********图片数量明细 END ********");

        return dataMap;
    }


    /**
     * 操作卡预览
     * @param result
     * @return
     * @throws Exception
     */
    public  static ByteArrayInputStream cardView(String title,Map<String,List<SingleWorkStep>> result) throws Exception {
        ByteArrayOutputStream bos=new ByteArrayOutputStream();
        InputStream in = OperateCard.class.getClassLoader().getResourceAsStream("step/操作卡预览模板.xls");
        List<CellRangeAddress> list = Lists.newArrayList();
        Workbook wb = new HSSFWorkbook(in);
        HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
        Map<String, StepIndex> position = getPosition(sheet,list);
        StepIndex advStepIndex = position.get(StepType.ADV.getCode());//风险的索引
        StepIndex merStepIndex = position.get(StepType.MER.getCode());//紧急索引
        StepIndex perStepIndex = position.get(StepType.PER.getCode());//准备索引
        StepIndex opeStepIndex = position.get(StepType.OPE.getCode());//操作索引
        StepIndex taiStepIndex = position.get(StepType.TAI.getCode());//结尾索引*/
        Iterator<SingleWorkStep> advIt = result.get("adv").iterator();
        Iterator<SingleWorkStep> mergeIt = result.get("merge").iterator();
        Iterator<SingleWorkStep> peparIt = result.get("pepare").iterator();
        Iterator<SingleWorkStep> operatIt = result.get("operate").iterator();
        Iterator<SingleWorkStep> tailIt = result.get("tail").iterator();
        int advCount=result.get("adv").size()-1<0?0:result.get("adv").size()-1;
        int merCount=result.get("merge").size()-1<0?0:result.get("merge").size()-1;
        int perpareCount=result.get("pepare").size()-1<0?0:result.get("pepare").size()-1;
        int operateCount=result.get("operate").size()-1<0?0:result.get("operate").size()-1;
        int tailCount=result.get("tail").size()-1<0?0:result.get("tail").size()-1;
        int begin=0;
        int end=0;
        /**
         * 风险阶段
         */
        begin=advStepIndex.getBegin();
        end=begin+advCount;
        advStepIndex.setBegin(begin);
        advStepIndex.setEnd(end);
        insertRow(sheet,begin,advCount,Arrays.asList(new Range(1,6)));
        if(begin<end){
            CellRangeAddress advTitleRange = new CellRangeAddress(begin, end, 0, 0);
            sheet.addMergedRegion(advTitleRange);
        }
        mergeRow(sheet,begin,Arrays.asList(new Range(1,6)));



        /**
         * 应急阶段
         */
        begin=end+1;
        end=begin+merCount;
        merStepIndex.setBegin(begin);
        merStepIndex.setEnd(end);
        insertRow(sheet,begin,merCount,Arrays.asList(new Range(1,6)));
        if(begin<end){
            CellRangeAddress merTitleRange = new CellRangeAddress(begin, end, 0, 0);
            sheet.addMergedRegion(merTitleRange);
        }
        mergeRow(sheet,begin,Arrays.asList(new Range(1,6)));



        /**
         * 准备阶段
         */
        begin=end+1;
        end=begin+perpareCount;
        perStepIndex.setBegin(begin);
        perStepIndex.setEnd(end);
        insertRow(sheet,begin,perpareCount,Arrays.asList(new Range(1,4),new Range(5, 6)));
        if(begin<end){
            CellRangeAddress perTitleRange = new CellRangeAddress(begin, end, 0, 0);
            sheet.addMergedRegion(perTitleRange);
        }
        mergeRow(sheet,begin,Arrays.asList(new Range(1,4)));



        /**
         * 操作阶段
         */
        begin=end+2;
        end=begin+operateCount;
        opeStepIndex.setBegin(begin);
        opeStepIndex.setEnd(end);
        insertRow(sheet,begin,operateCount,null);
        if(begin<end){
            CellRangeAddress opeTitleRange = new CellRangeAddress(begin, end, 0, 0);
            sheet.addMergedRegion(opeTitleRange);
        }

        /**
         * 结尾阶段
         */
        begin=end+1;
        end=begin+tailCount;
        if(tailCount>1){
            insertRow(sheet,begin,tailCount,Arrays.asList(new Range(1,4)));
            taiStepIndex.setBegin(begin);
            taiStepIndex.setEnd(end);
        }else{
            taiStepIndex.setBegin(begin);
            taiStepIndex.setEnd(begin);
        }
        if(begin<end){
                CellRangeAddress taiTitleRange = new CellRangeAddress(begin, end, 0, 0);
                sheet.addMergedRegion(taiTitleRange);
        }
        mergeRow(sheet,begin,Arrays.asList(new Range(1,4)));


        /**
         * 设置标题
         */
        HSSFRow titleRow = sheet.getRow(0);
        HSSFCell cell = titleRow.getCell(1);
        cell.setCellValue(title);


        /**
         * 风险阶段填充数据
         */
        setAdvValue(advStepIndex.getBegin(),advStepIndex.getEnd(),advIt,sheet,"card");
        /**
         * 应急阶段填充数据
         */
        setMergeValue(merStepIndex.getBegin(),merStepIndex.getEnd(),mergeIt,sheet,"card");
        /**
         * 准备阶段填充数据
         */
        setPeparValue(perStepIndex.getBegin(),perStepIndex.getEnd(),peparIt,sheet,"card");

        /**
         * 操作阶段填充数据
         */
        setOperateValue(opeStepIndex.getBegin(),opeStepIndex.getEnd(),operatIt,sheet,"card");

        /**
         * 收尾阶段填充数据
         */
        setTailValue(taiStepIndex.getBegin(),taiStepIndex.getEnd(),tailIt,sheet,"card");
        wb.write(bos);
        ByteArrayInputStream bis=new ByteArrayInputStream(bos.toByteArray());
        return  bis;
    }


    /**
     * 合并列单元格
     * @param sheet
     * @param starRow
     * @param list
     */
    private static void mergeRow(HSSFSheet sheet, int starRow,List<Range> list){
        //合并单元格(内容的单元格)
        if(list!=null&&list.size()>0){
            for (Range address : list) {
                CellRangeAddress rangeAddress=new CellRangeAddress(starRow,starRow,address.getMin(),address.getMax());
                sheet.addMergedRegion(rangeAddress);
            }
        }
    }


    /**
     * 巡回检查预览
    */
    public static ByteArrayInputStream turnView(String title,Map<String,List<SingleWorkStep>> result) throws Exception {
        ByteArrayOutputStream bos=new ByteArrayOutputStream();
        InputStream in = OperateCard.class.getClassLoader().getResourceAsStream("step/巡检质量标准预览模板.xls");
        List<CellRangeAddress> list = Lists.newArrayList();
        Workbook wb = new HSSFWorkbook(in);
        HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
        Map<String, StepIndex> position = getPosition(sheet,list);
        StepIndex advStepIndex = position.get(StepType.ADV.getCode());//风险的索引
        StepIndex merStepIndex = position.get(StepType.MER.getCode());//紧急索引
        StepIndex perStepIndex = position.get(StepType.PER.getCode());//准备索引
        StepIndex opeStepIndex = position.get(StepType.OPE.getCode());//操作索引
        StepIndex taiStepIndex = position.get(StepType.TAI.getCode());//结尾索引*/
        int advCount=result.get("adv").size()-1<0?0:result.get("adv").size()-1;
        int merCount=result.get("merge").size()-1<0?0:result.get("merge").size()-1;
        int perpareCount=result.get("pepare").size()-1<0?0:result.get("pepare").size()-1;
        int tailCount=result.get("tail").size()-1<0?0:result.get("tail").size()-1;
        Iterator<SingleWorkStep> advIt = result.get("adv").iterator();
        Iterator<SingleWorkStep> mergeIt = result.get("merge").iterator();
        Iterator<SingleWorkStep> peparIt = result.get("pepare").iterator();
        Iterator<SingleWorkStep> operatIt = result.get("operate").iterator();
        Iterator<SingleWorkStep> tailIt = result.get("tail").iterator();
        int begin=0;
        int end=0;
        Map<String,List<SingleWorkStep>> category= Maps.newHashMap();
        /**
         * 处理大的分类
         */
        for(SingleWorkStep singelWorkStep:result.get("operate")){
            if(category.get(singelWorkStep.getCategory())==null){
                List<SingleWorkStep> categoryList=Lists.newArrayList();
                categoryList.add(singelWorkStep);
                category.put(singelWorkStep.getCategory(),categoryList);
            }else{
                category.get(singelWorkStep.getCategory()).add(singelWorkStep);
            }
        }
        int categoryCount=category.size();
        List<Integer> asList =Lists.newArrayList();
        Iterator<String> iterator = category.keySet().iterator();
        while(iterator.hasNext()){
            asList.add(category.get(iterator.next()).size());
        }
        /**
         * 风险阶段
         */
        begin=advStepIndex.getBegin();
        end=begin+advCount;
        advStepIndex.setEnd(end);
        insertRow(sheet,begin,advCount,null);
        if(begin<=end){
            CellRangeAddress advTitleRange = new CellRangeAddress(begin, end, 0, 1);
            sheet.addMergedRegion(advTitleRange);
        }

        /**
         * 紧急阶段
         */
        begin=end+1;
        end=begin+merCount;
        merStepIndex.setBegin(begin);
        merStepIndex.setEnd(end);
        insertRow(sheet,begin,merCount,null);
        if(begin<=end){
            CellRangeAddress merTitleRange = new CellRangeAddress(begin, end, 0, 1);
            sheet.addMergedRegion(merTitleRange);
        }

        /**
         * 准备阶段
         */
        begin=end+1;
        end=begin+perpareCount;
        perStepIndex.setBegin(begin);
        perStepIndex.setEnd(end);
        insertRow(sheet,begin,perpareCount,null);
        if(begin<=end){
            CellRangeAddress perTitleRange = new CellRangeAddress(begin, end, 0, 1);
            sheet.addMergedRegion(perTitleRange);
        }
        /**
         *操作阶段
         */
        begin=end+2;
        int num=0;
        for(int k=0;k<asList.size();k++){
            num+=asList.get(k);
        }
        end=begin+num;
        end=end-1;
        opeStepIndex.setBegin(begin);
        opeStepIndex.setEnd(end);
        insertRow(sheet,begin,num-1,null);
        int firstRow=begin,lastRow=0;
        if(begin<=end){
            CellRangeAddress opeTitleRange = new CellRangeAddress(begin-1, end, 0, 0);
            sheet.addMergedRegion(opeTitleRange);
        }
        //合并大类单元格
        for(int i=0;i<categoryCount;i++){
            if(asList.get(i)==1){
                firstRow=firstRow+1;
            }else{
                lastRow=firstRow+asList.get(i)-1;
                CellRangeAddress clonumRaneg = new CellRangeAddress(firstRow, lastRow, 1, 1);
                sheet.addMergedRegion(clonumRaneg);
                firstRow=lastRow+1;
            }
        }

        /**
         * 结尾阶段
         */
        begin=end+1;
        end=begin+tailCount;
        taiStepIndex.setBegin(begin);
        taiStepIndex.setEnd(end);
        if(tailCount>1){
            insertRow(sheet,begin,tailCount,null);
            taiStepIndex.setBegin(begin);
            taiStepIndex.setEnd(end);
        }else{
            taiStepIndex.setBegin(begin);
            taiStepIndex.setEnd(begin);
        }
        if(begin<=end){
            CellRangeAddress taiTitleRange = new CellRangeAddress(begin, end, 0, 1);
            sheet.addMergedRegion(taiTitleRange);
        }

        /**
         * 设置标题
         */
        HSSFRow titleRow = sheet.getRow(0);
        HSSFCell cell = titleRow.getCell(1);
        cell.setCellValue(title);

        /**
         * 风险阶段填充数据
         */
        setAdvValue(advStepIndex.getBegin(),advStepIndex.getEnd(),advIt,sheet,"turn");
        /**
         * 应急阶段填充数据
         */
        setMergeValue(merStepIndex.getBegin(),merStepIndex.getEnd(),mergeIt,sheet,"turn");
        /**
         * 准备阶段填充数据
         */
        setPeparValue(perStepIndex.getBegin(),perStepIndex.getEnd(),peparIt,sheet,"turn");

        /**
         * 操作阶段填充数据
         */
        setOperateValue(opeStepIndex.getBegin(),opeStepIndex.getEnd(),operatIt,sheet,"turn");

        /**
         * 收尾阶段填充数据
         */
        setTailValue(taiStepIndex.getBegin(),taiStepIndex.getEnd(),tailIt,sheet,"turn");
        wb.write(bos);
        ByteArrayInputStream bis=new ByteArrayInputStream(bos.toByteArray());
        return  bis;
    }






    /**
     * 合并单元格行的列
     */
    static class Range{

        public Range(Integer min,Integer max){
            this.max=max;
            this.min=min;
        }
        private Integer min;
        private Integer max;
        public Integer getMin() {
            return min;
        }
        public void setMin(Integer min) {
            this.min = min;
        }
        public Integer getMax() {
            return max;
        }
        public void setMax(Integer max) {
            this.max = max;
        }


    }






    /**
     * 设置风险值
     * @param begin
     * @param end
     * @param it
     * @param sheet
     */
    private static void setAdvValue(int begin,int end,Iterator<SingleWorkStep> it,HSSFSheet sheet,String type) {
        for(int i=begin;i<=end;i++){
            HSSFRow row=sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            if(it.hasNext()){
                SingleWorkStep singleWorkStep=it.next();
                for (int j = 1; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    if("card".equals(type)){
                        if(j==1){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }else{
                        if(j==2){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }
                }
            }
        }
    }

    private static void setMergeValue(int begin,int end,Iterator<SingleWorkStep> it,HSSFSheet sheet,String type) {
        for(int i=begin;i<=end;i++){
            HSSFRow row=sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            if(it.hasNext()){
                SingleWorkStep singleWorkStep=it.next();
                for (int j = 1; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    if("card".equals(type)){
                        if(j==1){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }else{
                        if(j==2){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }
                }
            }

        }
    }

    private static void setPeparValue(int begin,int end,Iterator<SingleWorkStep> it,HSSFSheet sheet,String type) {
        for(int i=begin;i<=end;i++){
            HSSFRow row=sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            if(it.hasNext()){
                SingleWorkStep singleWorkStep=it.next();
                for (int j = 1; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    if("card".equals(type)){
                        if(j==1){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }else{
                        if(j==2){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }
                }
            }
        }
    }
    private static void setOperateValue(int begin,int end,Iterator<SingleWorkStep> it,HSSFSheet sheet,String type) {
        for(int i=begin;i<=end;i++){
            HSSFRow row=sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            if(it.hasNext()){
                SingleWorkStep singleWorkStep=it.next();
                for (int j = 1; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    if("card".equals(type)){
                        if(j==1){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }else if(j==2){
                            cell.setCellValue(singleWorkStep.getKeyPoint());
                        }else if(j==3){
                            cell.setCellValue(singleWorkStep.getPotentialRisk());
                        }else if(j==4){
                            cell.setCellValue(singleWorkStep.getControlMeasures());
                        }
                    }else{
                        if(j==1){
                            cell.setCellValue(singleWorkStep.getCategory());
                        }else if(j==2){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }

                }
            }

        }
    }
    private static void setTailValue(int begin,int end,Iterator<SingleWorkStep> it,HSSFSheet sheet,String type) {
        for(int i=begin;i<=end;i++){
            HSSFRow row=sheet.getRow(i);
            short lastCellNum = row.getLastCellNum();
            if(it.hasNext()){
                SingleWorkStep singleWorkStep=it.next();
                for (int j = 1; j < lastCellNum; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }

                    if("card".equals(type)){
                        if(j==1){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }else{
                        if(j==2){
                            cell.setCellValue(singleWorkStep.getStepName());
                        }
                    }
                }
            }

        }
    }



    /**
     * 插入一行
     *
     * @param sheet
     * @param starRow
     * @param rows
     * @param list
     */
    public static void insertRow(HSSFSheet sheet, int starRow, int rows,List<Range> list) {
        //移动指定的行
        sheet.shiftRows(starRow+1, sheet.getLastRowNum(), rows, true, false);
        //插入指定的行
        for (int i = 0; i < rows; i++) {
            HSSFRow sourceRow = null;
            HSSFRow targetRow = null;
            HSSFCell sourceCell = null;
            HSSFCell targetCell = null;
            int m;
            sourceRow = sheet.getRow(starRow);
            starRow=starRow+1;
            targetRow = sheet.createRow(starRow);
            targetRow.setHeight(sourceRow.getHeight());
            if(sourceRow.getRowStyle()!=null){
                targetRow.setRowStyle(sourceRow.getRowStyle());
            }
            for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
                sourceCell = sourceRow.getCell(m);
                targetCell = targetRow.createCell(m);
                if(sourceCell!=null){
                    targetCell.setCellStyle(sourceCell.getCellStyle());
                    targetCell.setCellType(sourceCell.getCellType());
                }
            }
            //合并单元格(内容的单元格)
            if(list!=null&&list.size()>0){
                for (Range address : list) {
                    CellRangeAddress rangeAddress=new CellRangeAddress(starRow,starRow,address.getMin(),address.getMax());
                    sheet.addMergedRegion(rangeAddress);
                }
            }

        }
    }

    public static void insertRow2(Workbook wb, HSSFSheet sheet, int starRow, int rows,List<Range> list) {
        sheet.shiftRows(starRow+1, sheet.getLastRowNum(), rows, true, false);

		/*starRow=starRow-1;*/

        for (int i = 0; i < rows; i++) {

            HSSFRow sourceRow = null;
            HSSFRow targetRow = null;
            HSSFCell sourceCell = null;
            HSSFCell targetCell = null;
            int m;

			/*starRow = starRow + 1;*/
            sourceRow = sheet.getRow(starRow);

            starRow=starRow+1;
            targetRow = sheet.createRow(starRow);
            targetRow.setHeight(sourceRow.getHeight());
//			targetRow.setRowStyle(sourceRow.getRowStyle());

            for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
					/*if(sourceCell==null||targetCell==null) continue;*/

                sourceCell = sourceRow.getCell(m);
                targetCell = targetRow.createCell(m);
                if(sourceCell!=null){
                    targetCell.setCellStyle(sourceCell.getCellStyle());
                    targetCell.setCellType(sourceCell.getCellType());
                }

            }

            for (Range address : list) {
                CellRangeAddress rangeAddress=new CellRangeAddress(starRow,starRow,address.getMin(),address.getMax());
                sheet.addMergedRegion(rangeAddress);
            }




        }
    }

    /**
     * 插入一行
     * @param wb
     * @param sheet
     * @param starRow
     * @param rows
     * @param list
     */
    public static void insertRow(Workbook wb, HSSFSheet sheet, int starRow, int rows,List<Range> list) {
        sheet.shiftRows(starRow+1, sheet.getLastRowNum(), rows, true, false);
        for (int i = 0; i < rows; i++) {
            HSSFRow sourceRow = null;
            HSSFRow targetRow = null;
            HSSFCell sourceCell = null;
            HSSFCell targetCell = null;
            int m;
            sourceRow = sheet.getRow(starRow);
            starRow=starRow+1;
            targetRow = sheet.createRow(starRow);
            targetRow.setHeight(sourceRow.getHeight());
            for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
                if(sourceCell==null||targetCell==null) continue;
                sourceCell = sourceRow.getCell(m);
                targetCell = targetRow.createCell(m);
                if(sourceCell!=null){
                    targetCell.setCellStyle(sourceCell.getCellStyle());
                    targetCell.setCellType(sourceCell.getCellType());
                }

            }
            for (Range address : list) {
                CellRangeAddress rangeAddress=new CellRangeAddress(starRow,starRow,address.getMin(),address.getMax());
                sheet.addMergedRegion(rangeAddress);
            }
        }
    }

    private static String  fixStr(String content){
        if(Strings.isNullOrEmpty(content)){
            return "";
        }
        int beginIndex=0;
        beginIndex=content.indexOf("．");
        if(beginIndex==-1){
            beginIndex=content.indexOf(".");
        }
        String result =content.substring(beginIndex+1);
        if(Strings.isNullOrEmpty(result)){
            return "";
        }
        return result;
    }

}
